package com.mmy.dao.restaurant;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.restaurant.VRestaurantPlan;

public interface VRestaurantPlanRepository extends JpaRepository<VRestaurantPlan, Integer> {

	/*
	 * 根据ScenicID查找
	 */
	@Query(value = "select *  from vRestaurantPlan where iScenicID = :iScenicID", nativeQuery = true)
	List<VRestaurantPlan> findByScenicID(@Param("iScenicID") int iScenicID);

	/*
	 * 根据ID查找
	 */
	@Query(value = "select *  from vRestaurantPlan where iPlanID = ?1", nativeQuery = true)
	VRestaurantPlan findByPlanID(int iPlanID);

	@Query(value = "select *  from vRestaurantPlan where iRestaurantID = ?1", nativeQuery = true)
	List<VRestaurantPlan> findByRestaurantID(int iRestaurantID);

	/**
	 * 前台景区菜单首页显示查询这些景区ID的计划日期大于当前日期的计划最小价格
	 * 
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.13
	 */
	@Query(value = "SELECT a.* FROM vRestaurantPlan a INNER JOIN ( SELECT iRestaurantID, MIN( fPrice ) AS fMinPrice FROM vRestaurantPlan  WHERE NOW( ) BETWEEN dStartDate AND dEndDate GROUP BY iRestaurantID ) b ON a.iRestaurantID = b.iRestaurantID AND a.fPrice = b.fMinPrice", nativeQuery = true)
	List<VRestaurantPlan> getMinPrice();

	/**
	 * 通过景区ID查询
	 *
	 * 后台查询该景区详细信息时用到 以开始时间倒序
	 * 
	 * @author 修改人 hanxl
	 * @date 创建日期2021.01.14
	 */
	@Query(value = "select *  from vRestaurantPlan where iRestaurantID = :iRestaurantID order by dStartDate desc ", nativeQuery = true)
	List<VRestaurantPlan> getByRestaurant(@Param("iRestaurantID") int iRestaurantID);

}
